package database.work;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils.InsertHelper;

import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper 
{

	/*
	 * These String reference the specific columns in the tables
	 * 12 including _id which is what you DON'T use when adding a row. _id is taken care of because of the autoincrement command in onCreate
	 */
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_LASTNAME = "Last_Name";
	public static final String COLUMN_FIRSTNAME = "First_Name";
	public static final String COLUMN_MIDDLE = "Middle_Name";
	public static final String COLUMN_TITLE = "Title";
	public static final String COLUMN_TITLE2 = "Title_2";
	public static final String COLUMN_DEPARTMENT = "Department";
	public static final String COLUMN_OFFICE = "Office_Location";
	public static final String COLUMN_PHONE = "Phone_Number";
	public static final String COLUMN_EMAIL = "Email_Address";
	public static final String COLUMN_BIO = "Bio";
	public static final String COLUMN_WEB = "Website";

	//Testing from Linux. Man linux is pretty awesome so far.

	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;

	/*
	 * The DATABASE_TABLE & DATABASE_TABLE2 reference the name of the tables so make sure that they are matching or you will have problems
	 */
	private static final String DATABASE_NAME = "ksu_directory";
	private static final String DIRECTORY_TABLE = "directory";
	private static final String DESC_TABLE = "desc";
	private static final int DATABASE_VERSION = 2;



	private final Context mContext;



	private static class DatabaseHelper extends SQLiteOpenHelper 
	{

		DatabaseHelper(Context context)
		{
			super (context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{

			db.execSQL("CREATE TABLE directory (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , Last_Name TEXT, First_Name TEXT, Middle_Name TEXT, Title TEXT, Title_2 " +
					"TEXT , Department TEXT , Office_Location TEXT , Phone_Number TEXT , Email_Address TEXT , Bio TEXT , Website TEXT );");
			db.execSQL("CREATE TABLE desc (_id INTEGER PRIMARY KEY AUTOINCREMENT , title TEXT , description TEXT );");

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
		{
			db.execSQL("DROP TABLE IF EXISTS directory");
			db.execSQL("DROP TABLE IF EXISTS desc");
			onCreate(db);
		}
	}
	/* Constructor - takes the context to allow the database to be
	 * opened/created
	 */
	public DBHelper(Context context)
	{
		this.mContext = context;
	}
	/*
	 * Open the directory database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 */
	public DBHelper open()  throws SQLException
	{
		dbHelper = new DatabaseHelper(mContext);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close()
	{
		dbHelper.close();
	}

	public void createDirectoryTable(String lastName, String firstName, String midName, String title, String title2, String department, String office, String pNum, String email)
	{
		InsertHelper ih = new InsertHelper(db, DIRECTORY_TABLE);

		final int LAST = ih.getColumnIndex(COLUMN_LASTNAME);
		final int FIRST = ih.getColumnIndex(COLUMN_FIRSTNAME);
		final int MIDDLE = ih.getColumnIndex(COLUMN_MIDDLE);
		final int T1 = ih.getColumnIndex(COLUMN_TITLE);
		final int T2 = ih.getColumnIndex(COLUMN_TITLE2);
		final int DPT = ih.getColumnIndex(COLUMN_DEPARTMENT);
		final int OFF = ih.getColumnIndex(COLUMN_OFFICE);
		final int PH = ih.getColumnIndex(COLUMN_PHONE);
		final int EM = ih.getColumnIndex(COLUMN_EMAIL);

		try
		{
			ih.prepareForInsert();

			ih.bind(LAST, lastName);
			ih.bind(FIRST, firstName);
			ih.bind(MIDDLE, midName);
			ih.bind(T1, title);
			ih.bind(T2, title2);
			ih.bind(DPT, department);
			ih.bind(OFF, office);
			ih.bind(PH, pNum);
			ih.bind(EM, email);

			ih.execute();
		}
		finally
		{
			ih.close();
		}

	}
	
	public boolean deleteNote(long rowId) {

		return db.delete(DIRECTORY_TABLE, COLUMN_TITLE + "=" + rowId, null) > 0;
	}

	/*
	 * Pulls all the entries of database.
	 * Ordered by Last Name
	 * 
	 * YOU HAVE TO INCLUDE COLUMN_ID IN THE CURSOR QUERY!
	 * It is there to help the cursor with position
	 * 
	 * Include every column that will use to display otherwise you will get "does not exist" errors
	 */
	public Cursor pullFullDirectory() 
	{
		return db.query(DIRECTORY_TABLE, new String[] {COLUMN_ID , COLUMN_LASTNAME,
				COLUMN_FIRSTNAME, COLUMN_MIDDLE, COLUMN_PHONE, COLUMN_TITLE, COLUMN_TITLE2, COLUMN_DEPARTMENT, COLUMN_OFFICE, COLUMN_EMAIL, COLUMN_BIO, COLUMN_WEB}, null, null, null, null, COLUMN_LASTNAME);
	}

	public Cursor fetchDescription(String selection)
	{
		return db.query(DIRECTORY_TABLE, new String[] {COLUMN_ID , COLUMN_LASTNAME,
				COLUMN_FIRSTNAME, COLUMN_MIDDLE, COLUMN_PHONE, COLUMN_TITLE, COLUMN_TITLE2, COLUMN_DEPARTMENT, COLUMN_OFFICE, COLUMN_EMAIL, COLUMN_BIO, COLUMN_WEB}, "Last_Name" + " LIKE '" + selection + "%'", null, null, null, COLUMN_LASTNAME);
	}

	public Cursor fetchDesc()
	{
		return db.query(DESC_TABLE, new String[] {COLUMN_TITLE, COLUMN_LASTNAME, COLUMN_DEPARTMENT}, null, null, null, null, COLUMN_TITLE);
	}

	/*
	 * Get entry at specified row (rowId)
	 */
	public Cursor fetchNote(long rowId) throws SQLException 
	{

		Cursor mCursor = db.query(true, DIRECTORY_TABLE, new String[] {COLUMN_TITLE, 
				COLUMN_LASTNAME, COLUMN_FIRSTNAME, COLUMN_TITLE2, COLUMN_PHONE}, COLUMN_TITLE + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) 
		{
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/*
	 * Update the note using the details provided. The note to be updated is
	 * specified using the rowId, and it is altered to use the title and body
	 * values passed in.
	 * 
	 * Not too complicated. Similar to the createDescription/createNote methods earlier
	 * update(table, values, whereClause, whereArgs)
	 * just leave the whereArgs null
	 * 
	 * @param rowId id of note to update
	 * @param title value to set note title to
	 * @param body value to set note body to
	 * @return true if the note was successfully updated, false otherwise
	 */
	public boolean updateNote(long rowId, String title, String body, long time, String pNumber)
	{
		ContentValues args = new ContentValues();
		args.put(COLUMN_LASTNAME, title);
		args.put(COLUMN_FIRSTNAME, body);
		args.put(COLUMN_TITLE2, time);
		args.put(COLUMN_PHONE, pNumber);

		return db.update(DIRECTORY_TABLE, args, COLUMN_TITLE + "=" + rowId, null) > 0;
	}
}